package cn.stylefeng.guns.modular.rec.service.impl;

import cn.hutool.core.collection.ListUtil;
import cn.stylefeng.guns.base.pojo.page.LayuiPageFactory;
import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.core.util.DateUtil;
import cn.stylefeng.guns.modular.basic.entity.BasicOperate;
import cn.stylefeng.guns.modular.basic.service.BasicOperateService;
import cn.stylefeng.guns.modular.hx.order.utils.JdbcUtls;
import cn.stylefeng.guns.modular.rec.entity.TransportDispatchVolMatch;
import cn.stylefeng.guns.modular.rec.mapper.TransportDispatchVolMatchMapper;
import cn.stylefeng.guns.modular.rec.model.params.TransportDispatchVolMatchParam;
import cn.stylefeng.guns.modular.rec.model.result.TransportDispatchVolMatchResult;
import  cn.stylefeng.guns.modular.rec.service.TransportDispatchVolMatchService;
import cn.stylefeng.guns.modular.tool.model.params.ToolExpressReconciliationRfParam;
import cn.stylefeng.guns.modular.tool.model.params.ToolExpressReconciliationYtParam;
import cn.stylefeng.guns.modular.transport.mapper.TransportDispatchMapper;
import cn.stylefeng.guns.modular.transport.model.result.TransportDispatchResult;
import cn.stylefeng.guns.sys.modular.system.entity.User;
import cn.stylefeng.guns.sys.modular.system.service.UserService;
import cn.stylefeng.roses.core.reqres.response.ResponseData;
import cn.stylefeng.roses.core.util.ToolUtil;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <p>
 * 客户订单体积匹配结果表 服务实现类
 * </p>
 *
 * @author zhangzh
 * @since 2024-10-18
 */
@Service
public class TransportDispatchVolMatchServiceImpl extends ServiceImpl<TransportDispatchVolMatchMapper, TransportDispatchVolMatch> implements TransportDispatchVolMatchService {

    @Autowired
    private DataSource dataSource;

    @Autowired
    private UserService userService;

    @Autowired
    private TransportDispatchMapper transportDispatchMapper;

    @Autowired
    private BasicOperateService basicOperateService;

    @Override
    public void add(TransportDispatchVolMatchParam param){
        TransportDispatchVolMatch entity = getEntity(param);
        this.save(entity);
    }

    @Override
    public void delete(TransportDispatchVolMatchParam param){
        this.removeById(getKey(param));
    }

    @Override
    public void update(TransportDispatchVolMatchParam param){
        TransportDispatchVolMatch oldEntity = getOldEntity(param);
        TransportDispatchVolMatch newEntity = getEntity(param);
        ToolUtil.copyProperties(newEntity, oldEntity);
        this.updateById(newEntity);
    }

    @Override
    public TransportDispatchVolMatchResult findBySpec(TransportDispatchVolMatchParam param){
        return null;
    }

    @Override
    public List<TransportDispatchVolMatchResult> findListBySpec(TransportDispatchVolMatchParam param){
        return this.baseMapper.customList(param);
    }
    @Override
    public List<TransportDispatchVolMatchResult> findTranListBySpec(TransportDispatchVolMatchParam param){
        return this.baseMapper.customTranList(param);
    }



    @Override
    public LayuiPageInfo findPageBySpec(TransportDispatchVolMatchParam param,String condition){
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customPageList(pageContext, param,condition);
        return LayuiPageFactory.createPageInfo(page);
    }
    @Override
    public LayuiPageInfo findTranPageBySpec(TransportDispatchVolMatchParam param,String condition){
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customTranPageList(pageContext, param,condition);
        return LayuiPageFactory.createPageInfo(page);
    }

    @Override
    public LayuiPageInfo customTran2PageList(TransportDispatchVolMatchParam param,String condition){
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customTran2PageList(pageContext, param,condition);
        return LayuiPageFactory.createPageInfo(page);
    }

    @Override
    public LayuiPageInfo customTran3PageList(TransportDispatchVolMatchParam param,String condition){
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customTran3PageList(pageContext, param,condition);
        return LayuiPageFactory.createPageInfo(page);
    }

    @Override
    public ResponseData getExceldata(MultipartFile file,Long operateId) {
        User user = userService.getcurrentUser();
        SimpleDateFormat sdf20 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        ResponseData data = ResponseData.success();
        String fileName = file.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            data.setMessage("上传文件格式不正确");
            data.setSuccess(false);
            return data;
        }
        if(ToolUtil.isEmpty(operateId)){
            data.setMessage("请选择项目");
            data.setSuccess(false);
            return data;
        }
        //获取项目信息
        BasicOperate basicOperate = this.basicOperateService.getById(operateId);

        Connection conn = null;
        PreparedStatement psInserTmp = null;
        PreparedStatement psSelete = null;
        PreparedStatement psSelVolMatch = null;
        PreparedStatement psSelVolMatchLs = null;
        PreparedStatement psInsertTmpTableByLs = null;
        PreparedStatement psDelLsByOperateId = null;
        PreparedStatement psInsertRs = null;
        PreparedStatement psInsertRsLs = null;
        PreparedStatement psSelVolMatchSucess = null;
        PreparedStatement psUpdateOrderTransportMatchRs = null;
        PreparedStatement psUpdateDispatchMatchRs = null;

        ResultSet rs=null;
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            boolean isExcel2003 = true;
            if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                isExcel2003 = false;
            }
            InputStream is = file.getInputStream();
            Workbook wb = null;
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {
                wb = new XSSFWorkbook(is);
            }
            Sheet sheet = wb.getSheetAt(0);

            //创建临时表
            conn.prepareStatement("DROP TEMPORARY table IF EXISTS imp_tep_transport_dispatch_vol_match").execute();
            conn.prepareStatement(this.getCreateTmpTableSql()).execute();
            psInserTmp = conn.prepareStatement(this.getInsertTmpTableSql());
            psSelete = conn.prepareStatement(this.getSeleteTmpExistsTableSql());
            psSelVolMatch = conn.prepareStatement(this.getSelVolMatchSql());
            psSelVolMatchLs = conn.prepareStatement(this.getSelVolMatchLsSql());
            psInsertTmpTableByLs = conn.prepareStatement(this.getInsertTmpTableByLsSql());
            psDelLsByOperateId = conn.prepareStatement(this.getDelLsByOperateIdSql());
            psInsertRs = conn.prepareStatement(this.getInsertRsTableSql());
            psInsertRsLs = conn.prepareStatement(this.getInsertRsLsTableSql());
            psSelVolMatchSucess = conn.prepareStatement(this.getSelVolMatchSucessSql());
            //添加临时表数据
            int rows = 0;
            for (int r = 1; r <= sheet.getLastRowNum()+1; r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                String customerOrder = DateUtil.getCellValueByCell(row.getCell(0));//客户单号
                if (ToolUtil.isNotEmpty(customerOrder)) {
                    psInserTmp.setString(1,customerOrder);
                } else {
                    continue;
                }

                String custVol = DateUtil.getCellValueByCell(row.getCell(1));//客户体积
                if (ToolUtil.isNotEmpty(custVol)) {
                    psInserTmp.setBigDecimal(2,new BigDecimal(custVol).setScale(4, BigDecimal.ROUND_HALF_UP));
                } else {
                    continue;
                }

                String settlementMonth = DateUtil.getCellValueByCell(row.getCell(2));//结算月份
                if (ToolUtil.isNotEmpty(custVol)) {
                    psInserTmp.setString(3,settlementMonth);
                } else {
                    continue;
                }
                psInserTmp.setLong(4,operateId);
                rows++;
                psInserTmp.addBatch();
                if(rows > 1000){
                    psInserTmp.executeBatch();
                    rows=0;
                }
            }
            if(rows > 0){
                psInserTmp.executeBatch();
            }
            //判断是否有重复  customer_order,settlement_month
            rs = psSelete.executeQuery();
            StringBuffer errOrderStr = new StringBuffer();
            while(rs.next()){
                if(errOrderStr.length() == 0){
                    errOrderStr.append(rs.getString("customer_order"))
                            .append("(").append(rs.getString("settlement_month")).append(")");
                }else{
                    errOrderStr.append(",").append(rs.getString("customer_order"))
                            .append("(").append(rs.getString("settlement_month")).append(")");;
                }
            }
            if(errOrderStr.length() > 0){
                JdbcUtls.rollback(conn);
                return ResponseData.error("导入失败：导入数据中客户单号["+errOrderStr.toString()+"]有重复，请修改后重新操作");
            }
            //判断是否重复导入订单 匹配结果表
            rs = psSelVolMatch.executeQuery();
            while(rs.next()){
                if(errOrderStr.length() == 0){
                    errOrderStr.append(rs.getString("customer_order"))
                            .append("(").append(rs.getString("settlement_month")).append(")");
                }else{
                    errOrderStr.append(",").append(rs.getString("customer_order"))
                            .append("(").append(rs.getString("settlement_month")).append(")");;
                }
            }
            //判断是否重复导入订单 未匹配表
            rs = psSelVolMatchLs.executeQuery();
            while(rs.next()){
                if(errOrderStr.length() == 0){
                    errOrderStr.append(rs.getString("customer_order"))
                            .append("(").append(rs.getString("settlement_month")).append(")");
                }else{
                    errOrderStr.append(",").append(rs.getString("customer_order"))
                            .append("(").append(rs.getString("settlement_month")).append(")");;
                }
            }
            if(errOrderStr.length() > 0){
                JdbcUtls.rollback(conn);
                return ResponseData.error("导入失败：客户单号["+errOrderStr.toString()+"]已存在，请修改后重新操作");
            }
            //将历史未匹配加入当前匹配数据中重新批量
            psInsertTmpTableByLs.setLong(1,operateId);
            psInsertTmpTableByLs.execute();
            //删除当前未匹配历史
            psDelLsByOperateId.setLong(1,operateId);
            psDelLsByOperateId.execute();
            //插入匹配结果表
            psInsertRs.setLong(1,user.getUserId());
            psInsertRs.execute();
            //插入未存在客户表
            psInsertRsLs.setLong(1,user.getUserId());
            psInsertRsLs.setLong(2,operateId);
            psInsertRsLs.setString(3,basicOperate.getOperateName());
            psInsertRsLs.setLong(4,user.getDeptId());
            psInsertRsLs.execute();
            //获取本次匹配成功的客户单号
            StringBuffer custOrderStr = new StringBuffer();
            rs = psSelVolMatchSucess.executeQuery();
            while(rs.next()){
                if(custOrderStr.length() < 1){
                    custOrderStr.append(rs.getString(1));
                }else{
                    custOrderStr.append(",").append(rs.getString(1));
                }
            }
            //有匹配成功的客户号
            if(custOrderStr.length() > 0){
                //修改货运订单
                psUpdateOrderTransportMatchRs = conn.prepareStatement(this.getUpdateOrderTransportMatchRsSql(custOrderStr.toString()));
                psUpdateOrderTransportMatchRs.execute();
                //修改运输单
                psUpdateDispatchMatchRs = conn.prepareStatement(this.getUpdateDispatchMatchRsSql(custOrderStr.toString()));
                psUpdateDispatchMatchRs.execute();
            }
            //事务提交
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            JdbcUtls.rollback(conn);
            return ResponseData.error("导入失败："+ e.getMessage());
        }finally {
            JdbcUtls.closeResultSet(rs);
            JdbcUtls.closePreparedStatement(psInserTmp);
            JdbcUtls.closePreparedStatement(psSelete);
            JdbcUtls.closePreparedStatement(psSelVolMatch);
            JdbcUtls.closePreparedStatement(psSelVolMatchLs);
            JdbcUtls.closePreparedStatement(psInsertTmpTableByLs);
            JdbcUtls.closePreparedStatement(psDelLsByOperateId);
            JdbcUtls.closePreparedStatement(psInsertRs);
            JdbcUtls.closePreparedStatement(psInsertRsLs);
            JdbcUtls.closePreparedStatement(psUpdateOrderTransportMatchRs);
            JdbcUtls.closePreparedStatement(psUpdateDispatchMatchRs);
            JdbcUtls.closeConnection(conn);
        }
        return data;
    }

    /**
     * 替换体积
     * @param ids
     * @return
     */
    @Override
    public ResponseData replaceOption(String ids) {
        if(ToolUtil.isEmpty(ids)){
            return ResponseData.error("请选择替换体积数据");
        }
        User user = userService.getcurrentUser();
        StringBuffer custOrderIds = new StringBuffer();
        //查询查询已选择数据
        List<String> list = ListUtil.of(ids.split(","));
        List<TransportDispatchVolMatchResult> transportDispatchVolMatchs = this.baseMapper.findByIds(list);
        //获取当前体积
        for(TransportDispatchVolMatchResult obj : transportDispatchVolMatchs){
            if(custOrderIds.length() == 0){
                custOrderIds.append(obj.getCustomerOrder());
            }else{
                custOrderIds.append(",").append(obj.getCustomerOrder());
            }
        }
        List<String> listOrderIds = ListUtil.of(custOrderIds.toString().split(","));
        List<TransportDispatchResult> transportDispatchs = transportDispatchMapper.findByCustOrderIds(listOrderIds);

        StringBuffer dispatchNumberIds = new StringBuffer();
        for(TransportDispatchResult obj : transportDispatchs){
            if(dispatchNumberIds.length() == 0){
                dispatchNumberIds.append(obj.getDispatchNumber());
            }else{
                dispatchNumberIds.append(",").append(obj.getDispatchNumber());
            }
        }

        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            //修改【order_transport】表
            conn.prepareStatement(this.getUpdateOrderTransportSql(custOrderIds.toString())).execute();
            //修改【transport_dispatch】表
            conn.prepareStatement(this.getUpdateTransportDispatchSql(custOrderIds.toString())).execute();
            //修改【t_dispatch】表
            conn.prepareStatement(this.getUpdateTDispatchSql(dispatchNumberIds.toString())).execute();
            //修改【transport_dispatch_vol_match】表
            ps = conn.prepareStatement(this.getUpdateTransportDispatchVolMatchSql(ids));
            ps.setString(1,"是");
            ps.setString(2,user.getName());
            ps.execute();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            JdbcUtls.rollback(conn);
            return ResponseData.error("导入失败："+ e.getMessage());
        }finally {
            JdbcUtls.closeAll(null,ps,conn);
        }

        return ResponseData.success();
    }

    @Override
    public ResponseData delAll(String ids) {
        if(ToolUtil.isEmpty(ids)){
            return ResponseData.error("请选择要删除的数据");
        }
        //查询查询已选择数据
        List<String> list = ListUtil.of(ids.split(","));
        this.baseMapper.delAll(list);
        return ResponseData.success();
    }

    /**
     * 修改【order_transport】表
     * @param ids
     * @return
     */
    private String getUpdateOrderTransportSql(String ids){
        StringBuffer sql = new StringBuffer();
        sql.append("update order_transport t1 ");
        sql.append("inner join transport_dispatch_vol_match t2 ");
        sql.append("on t1.customer_order = t2.customer_order ");
        sql.append("set t1.totalvolume = t2.cust_vol ");
        sql.append(",t1.toovolume = ROUND(t1.toovolume/t1.totalvolume * t2.cust_vol,4) ");
        sql.append("where  t1.customer_order = t2.customer_order ");
        sql.append("and t1.customer_order in ('");
        sql.append(ids.replaceAll(",","','"));
        sql.append("') ");
        System.out.println(sql.toString());
        return sql.toString();
    }

    /**
     * 修改【transport_dispatch】表
     * @param ids
     * @return
     */
    private String getUpdateTransportDispatchSql(String ids){
        StringBuffer sql = new StringBuffer();
        sql.append("update transport_dispatch t1 ");
        sql.append("inner join transport_dispatch_vol_match t2 ");
        sql.append("on t1.customer_order = t2.customer_order ");
        sql.append("set t1.tooDeployVolume = ROUND(t1.tooDeployVolume/t2.sys_toovolume *t2.cust_vol,4) ");
        sql.append("where  t1.customer_order = t2.customer_order ");
        sql.append("and t1.customer_order in ('");
        sql.append(ids.replaceAll(",","','"));
        sql.append("') ");
        System.out.println(sql.toString());
        return sql.toString();
    }

    /**
     * 修改【t_dispatch】表
     * @param ids
     * @return
     */
    private String getUpdateTDispatchSql(String ids){
        StringBuffer sql = new StringBuffer();
        sql.append("update t_dispatch t1 ");
        sql.append("inner join ( ");
        sql.append("select tt1.dispatch_number, ROUND(sum(tt1.tooDeployVolume),4) as tooDeployVolume ");
        sql.append("from transport_dispatch tt1 ");
        sql.append("where tt1.dispatch_number in('");
        sql.append(ids.replaceAll(",","','"));
        sql.append("') ");
        sql.append(")rs on t1.dispatch_number = rs.dispatch_number ");
        sql.append("set t1.toovolume = rs.tooDeployVolume ");
        sql.append("where t1.dispatch_number = rs.dispatch_number ");
        System.out.println(sql.toString());
        return sql.toString();
    }

    /**
     * 修改状态
     * @param ids
     * @return
     */
    private String getUpdateTransportDispatchVolMatchSql(String ids){
        StringBuffer sql = new StringBuffer();
        sql.append("update transport_dispatch_vol_match ");
        sql.append("set replace_flag = ? ");
        sql.append(",replace_user = ? ");
        sql.append(",replace_time = now() ");
        sql.append("where id in (");
        sql.append(ids);
        sql.append(") ");
        System.out.println(sql.toString());
        return sql.toString();
    }


    /**
     * 创建临时表，存导入的数据
     * @return
     */
    private String getCreateTmpTableSql(){
        StringBuffer sql = new StringBuffer();
        sql.append("CREATE TEMPORARY TABLE imp_tep_transport_dispatch_vol_match (");
        sql.append("customer_order varchar(32)");
        sql.append(",cust_vol decimal(18,4) ");
        sql.append(",settlement_month varchar(20) ");
        sql.append(",operate_id bigint ");
        sql.append(") ");
        return sql.toString();
    }

    /**
     * 插入临时表
     * @return
     */
    private String getInsertTmpTableSql(){
        StringBuffer sql = new StringBuffer();
        sql.append("insert into imp_tep_transport_dispatch_vol_match (");
        sql.append("customer_order");
        sql.append(",cust_vol");
        sql.append(",settlement_month");
        sql.append(",operate_id");
        sql.append(") values(?,?,?,?) ");
        return sql.toString();
    }

    /**
     * 按项目重新匹配历史
     * @return
     */
    private String getInsertTmpTableByLsSql(){
        StringBuffer sql = new StringBuffer();
        sql.append("insert into imp_tep_transport_dispatch_vol_match (");
        sql.append("customer_order");
        sql.append(",settlement_month");
        sql.append(",cust_vol");
        sql.append(",operate_id");
        sql.append(") ");
        sql.append("select  customer_order ");
        sql.append(",settlement_month");
        sql.append(",cust_vol");
        sql.append(",operate_id ");
        sql.append("from transport_dispatch_vol_match_ls ");
        sql.append("where operate_id = ? ");
        return sql.toString();
    }

    private String getDelLsByOperateIdSql(){
        StringBuffer sql = new StringBuffer();
        sql.append("delete from transport_dispatch_vol_match_ls ");
        sql.append("where operate_id = ? ");
        return sql.toString();
    }

    /**
     *
     * @return
     */
    private String getSeleteTmpExistsTableSql(){
        StringBuffer sql = new StringBuffer();
        sql.append("select customer_order,settlement_month ");
        sql.append("from imp_tep_transport_dispatch_vol_match ");
        sql.append("group by customer_order,settlement_month ");
        sql.append("having count(1) > 1 ");
        return sql.toString();
    }

    /**
     * 删除本次已导入的客户单号
     * @return
     */
    private String getDelTableSql(){
        StringBuffer sql = new StringBuffer();
        sql.append("delete from transport_dispatch_vol_match ");
        sql.append("where customer_order in (");
        sql.append("select customer_order from imp_tep_transport_dispatch_vol_match ");
        sql.append(")");
        return sql.toString();
    }


    /**
     * 插入结果表
     * @return
     */
    private String getInsertRsTableSql(){
        StringBuffer sql = new StringBuffer();
        sql.append("insert into transport_dispatch_vol_match( ");
        sql.append("customer_order");
        sql.append(",transport_number");
        sql.append(",cust_vol");
        sql.append(",sys_vol");
        sql.append(",sys_toovolume");
        sql.append(",vol_flag");
        sql.append(",vol_flag_remark");
        sql.append(",create_time");
        sql.append(",create_user");
        sql.append(",operate_id");
        sql.append(",operate_name");
        sql.append(",dept_id");
        sql.append(",dispatch_date");
        sql.append(",settlement_month");
        sql.append(") ");
        sql.append("select ");
        sql.append("t1.customer_order");
        sql.append(",t2.transport_number");
        sql.append(",t1.cust_vol");
        sql.append(",t2.totalvolume");
        sql.append(",t2.toovolume");
        sql.append(",case when t2.id is null then '否' else IF(ABS(t1.cust_vol - t2.totalvolume) > 0.01,'否','是') end as vol_flag");
        sql.append(",case when t2.id is null then '客户单号不存在' else IF(ABS(t1.cust_vol - t2.totalvolume) > 0.01,'体积差异大于0.01','匹配成功') end as vol_flag_remark");
        sql.append(",CURRENT_TIMESTAMP as create_time");
        sql.append(",? as create_user");
        sql.append(",t2.operate_id");
        sql.append(",t2.operate_name");
        sql.append(",t2.dept_id");
        sql.append(",DATE_FORMAT(t2.undertime,'%Y-%m-%d') as undertime ");
        sql.append(",t1.settlement_month ");
        sql.append("from imp_tep_transport_dispatch_vol_match t1 ");
        sql.append("inner join order_transport t2 ");
        sql.append("on t1.customer_order = t2.customer_order ");
        sql.append("and t2.tstate !='作废' ");
        return sql.toString();
    }


    /**
     * 修改匹配结果
     * @param custOrders
     * @return
     */
    private String getUpdateOrderTransportMatchRsSql(String custOrders){
        StringBuffer sql = new StringBuffer();
        sql.append("update order_transport ");
        sql.append("set match_rs='已结算' ");
        sql.append("where customer_order in('");
        sql.append(custOrders.replaceAll(",","','"));
        sql.append("')");
        return sql.toString();
    }

    /**
     * 修改运输单
     * @param custOrders
     * @return
     */
    private String getUpdateDispatchMatchRsSql(String custOrders){
        StringBuffer sql = new StringBuffer();
        sql.append("update t_dispatch t ");
        sql.append("inner join ( ");
        sql.append("select ");
        sql.append("tt1.dispatch_number ");
        sql.append("from transport_dispatch tt1 ");
        sql.append("inner join transport_dispatch tt2 ");
        sql.append("on tt1.dispatch_number = tt2.dispatch_number ");
        sql.append("inner join order_transport tt3 ");
        sql.append("on tt2.customer_order = tt3.customer_order ");
        sql.append("where tt1.customer_order in('");
        sql.append(custOrders.replaceAll(",","','"));
        sql.append("')");
        sql.append("group by tt1.dispatch_number ");
        sql.append("having sum(IF(tt3.match_rs = '待结算',1,0)) = 0 ");
        sql.append(")rs ");
        sql.append("on t.dispatch_number = rs.dispatch_number ");
        sql.append("set t.match_rs = '已结算' ");
        sql.append("where t.dispatch_number = rs.dispatch_number ");
        return sql.toString();
    }

    /**
     * 批量插入未匹配成功表
     * @return
     */
    private String getInsertRsLsTableSql(){
        StringBuffer sql = new StringBuffer();
        sql.append("insert into transport_dispatch_vol_match_ls( ");
        sql.append("customer_order");
        sql.append(",cust_vol");
        sql.append(",create_time");
        sql.append(",create_user");
        sql.append(",operate_id");
        sql.append(",operate_name");
        sql.append(",dept_id");
        sql.append(",settlement_month");
        sql.append(") ");
        sql.append("select ");
        sql.append("t1.customer_order");
        sql.append(",t1.cust_vol");
        sql.append(",CURRENT_TIMESTAMP as create_time");
        sql.append(",? as create_user");
        sql.append(",? as operate_id ");
        sql.append(",? as operate_name ");
        sql.append(",? as dept_id ");
        sql.append(",t1.settlement_month ");
        sql.append("from imp_tep_transport_dispatch_vol_match t1 ");
        sql.append("left join order_transport t2 ");
        sql.append("on t1.customer_order = t2.customer_order ");
        sql.append("and t2.tstate !='作废' ");
        sql.append("where t2.id is null ");
        return sql.toString();
    }


    /**
     * 获取本次成功匹配的客户单号
     * @return
     */
    private String getSelVolMatchSucessSql(){
        StringBuffer sql = new StringBuffer();
        sql.append("select ");
        sql.append("t1.customer_order ");
        sql.append("from imp_tep_transport_dispatch_vol_match t1 ");
        sql.append("inner join transport_dispatch_vol_match t2 ");
        sql.append("on t1.customer_order = t2.customer_order ");
        sql.append("and t1.settlement_month = t2.settlement_month ");
        sql.append("where t2.vol_flag_remark in('体积差异大于0.01','匹配成功') ");
        sql.append("group by t1.customer_order ");
        return sql.toString();
    }

    /**
     * 判断是否重复导入订单 匹配结果表
     * @return
     */
    private String getSelVolMatchSql(){
        StringBuffer sql = new StringBuffer();
        sql.append("select ");
        sql.append("t1.customer_order ");
        sql.append(",t1.settlement_month ");
        sql.append("from imp_tep_transport_dispatch_vol_match t1 ");
        sql.append("inner join transport_dispatch_vol_match t2 ");
        sql.append("on t1.customer_order = t2.customer_order ");
        sql.append("and t1.settlement_month = t2.settlement_month ");
        return sql.toString();
    }

    /**
     * 判断是否重复导入订单 未匹配
     * @return
     */
    private String getSelVolMatchLsSql(){
        StringBuffer sql = new StringBuffer();
        sql.append("select ");
        sql.append("t1.customer_order ");
        sql.append(",t1.settlement_month ");
        sql.append("from imp_tep_transport_dispatch_vol_match t1 ");
        sql.append("inner join transport_dispatch_vol_match_ls t2 ");
        sql.append("on t1.customer_order = t2.customer_order ");
        sql.append("and t1.settlement_month = t2.settlement_month ");
        return sql.toString();
    }


    /**
     * 新增未匹配成功的账单
     * @return
     */
    private String getInsertTransportDispatchVolMatchLsSql(){
        StringBuffer sql = new StringBuffer();
        sql.append("insert into transport_dispatch_vol_match_ls( ");
        sql.append("customer_order");
        sql.append(",settlement_month");
        sql.append(",cust_vol");
        sql.append(",create_time");
        sql.append(",create_user");
        sql.append(",operate_id");
        sql.append(",operate_name");
        sql.append(",dept_id");
        sql.append(") ");
        sql.append("values(?,?,?,?,?,?,?,?) ");
        return sql.toString();
    }
    /**
     * 获取本次匹配结果
     * @return
     */
    private String getMatchRsSql(){
        StringBuffer sql = new StringBuffer();
        sql.append("select ");
        sql.append("t1.customer_order");
        sql.append(",t2.transport_number");
        sql.append(",t1.cust_vol");
        sql.append(",t2.totalvolume");
        sql.append(",t2.toovolume");
        sql.append(",case when t2.id is null then '否' else IF(ABS(t1.cust_vol - t2.totalvolume) > 0.01,'否','是') end as vol_flag");
        sql.append(",case when t2.id is null then '客户单号不存在' else IF(ABS(t1.cust_vol - t2.totalvolume) > 0.01,'体积差异大于0.01','匹配成功') end as vol_flag_remark");
        sql.append(",CURRENT_TIMESTAMP as create_time");
        sql.append(",t2.operate_id");
        sql.append(",t2.operate_name");
        sql.append(",t2.dept_id");
        sql.append(",t2.undertime");
        sql.append(",t1.settlement_month ");
        sql.append("from imp_tep_transport_dispatch_vol_match t1 ");
        sql.append("left join order_transport t2 ");
        sql.append("on t1.customer_order = t2.customer_order ");
        sql.append("and t2.tstate !='作废' ");
        sql.append("and t1.operate_id = t2.operate_id ");
        return sql.toString();
    }

    private Serializable getKey(TransportDispatchVolMatchParam param){
        return param.getId();
    }

    private Page getPageContext() {
        return LayuiPageFactory.defaultPage();
    }

    private TransportDispatchVolMatch getOldEntity(TransportDispatchVolMatchParam param) {
        return this.getById(getKey(param));
    }

    private TransportDispatchVolMatch getEntity(TransportDispatchVolMatchParam param) {
        TransportDispatchVolMatch entity = new TransportDispatchVolMatch();
        ToolUtil.copyProperties(param, entity);
        return entity;
    }

}
